package com.koo.modules.quartz.server.impl;

import cn.hutool.core.date.DateUtil;
import com.koo.common.utils.MyDateUtil;
import com.koo.common.utils.MyStrUtil;
import com.koo.modules.bja.entity.*;
import com.koo.modules.bja.service.*;
import com.koo.modules.quartz.constant.B2BConstant;
import com.koo.modules.quartz.constant.B2BTimeConstant;
import com.koo.modules.quartz.model.StoreModel;
import com.koo.modules.quartz.server.BaseThirdDataService;
import com.koo.modules.quartz.util.ChangeUtil;
import com.koo.modules.quartz.util.MysqlJDBCUtil;
import com.koo.modules.quartz.util.SqlJDBCUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Component
@Slf4j
public class BJAThirdDataServiceImpl implements BaseThirdDataService {

//    private final String[] ENVIRONMENT_CONNECTION_PREFIXS = AdminConstant.ALL_ENVIRONMENT_CONNECTION_PREFIXS.getPrefixDatas();
    //同步
    @Autowired
    private BjaProductService bjaProductService;
    @Autowired
    private BjaStoreInfoService bjaStoreInfoService;
    @Autowired
    private BjaOrderOutService bjaOrderOutService;

    //转换
    @Autowired
    private BjaInspectionOrderService bjaInspectionOrderService;
    @Autowired
    private BjaInspectionOrderDetailService bjaInspectionOrderDetailService;

    //上传
    @Autowired
    private BjaUploadMemberDataService bjaUploadMemberDataService;
    @Autowired
    private BjaUploadSalesDataService bjaUploadSalesDataService;
    @Autowired
    private BjaUploadStockDataService bjaUploadStockDataService;


    private final String DEFAULT_DOWNLOAD_ORDER_OUT_TIME = "2019-04-01 00:00:00";//没有出库单据时, 默认下载时间 | 默认上传开始时间

    @Override
    @Transactional
    public void storeDataTransport() {
        //验证当前的企业是否开启了同步机制
        if (!checkNeedThirdData()) {
            return;
        }
        Date maxCreateDate = bjaStoreInfoService.selectMaxCreateDate();
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxCreateDate)) {
            addDate = DateUtil.format(maxCreateDate, B2BTimeConstant.DEFAULT_TIME_FORMAT.getFormat());
        }
        //1.从ZY查询数据
        String sql = "SELECT 'ZDA' AS company_id, duifbsh AS store_code, 1 AS store_type, duifname AS store_name, duifname AS store_full_name, tw_mrxshy AS contactor, address AS store_address, lastmodifytime as create_time, GETDATE() as update_time, 1 as status FROM lsdtxb WHERE duifbsh LIKE 'a%' {WHERE_LIST};";
//        String sql = B2BConstant.BJA_PULL_STORE_SQL.getSql();
        if (MyStrUtil.isNotEmpty(addDate)) {
            sql = sql.replace("{WHERE_LIST}", " AND lastmodifytime >= '" + addDate + "' ");
        } else {
            sql = sql.replace("{WHERE_LIST}", " ");
        }
        Connection connection = null;
        List<BjaStoreInfoEntity> infos = new ArrayList<>();
        try {
            connection = SqlJDBCUtil.getConnection(B2BConstant.BJA.getCompanyCode());
            ResultSet rs = SqlJDBCUtil.execQuery(sql, connection);
            infos = ChangeUtil.changeResultToList(rs, BjaStoreInfoEntity.class);
        } finally {
            SqlJDBCUtil.commit(connection);
        }
        //2.插入到对应前缀的门店表
        //1.删除已经存在的
        //2.批量插入
        //删除和插入在service事务执行
        if (infos.isEmpty()) {
            return;
        }
        bjaStoreInfoService.saveOrUpdateBatch(infos);
    }

    @Override
    public void productDataTransport() {
        if (!checkNeedThirdData()) {
            return;
        }
        Date maxModifyDate = bjaProductService.selectMaxCreateDate();
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxModifyDate)) {
            addDate = MyDateUtil.formatDateTime(maxModifyDate);
        }
        //1.从ZY查询商品数据
//        String sql = B2BConstant.BJA_PULL_PRODUCT_SQL.getSql();
        String sql = "SELECT TOP 5000 leibie AS category_name, spbh AS product_code, spmch AS product_name, tongym AS generic_name, shpchd AS producing_area, shpgg AS standard, dw AS unit, zjm AS mnemonic_name, jj AS trade_price, lshj AS retail_price, lshj AS local_retail_price, huiytj AS member_price, sptm AS bar_code, sptm AS local_bar_code, CASE WHEN chuffl = '处方用药' THEN 1 ELSE 0 END AS is_rx_drug, pizhwh AS approval_no, shengccj AS produce_factory, jixing AS dosage_form, chuctj AS storage_condition, bwm AS standard_code, baozhgg AS province_standard, CASE WHEN youxq LIKE '%个月' THEN SUBSTRING (youxq, 0, len(youxq) - 1) WHEN youxq LIKE '%月' THEN SUBSTRING (youxq, 0, len(youxq)) WHEN youxq LIKE '%-%' OR youxq = '.' OR youxq = '' THEN NULL ELSE youxq END AS expiry_date, CASE WHEN is_ybsp = '是' THEN 1 ELSE 0 END AS is_yi_bao, CASE WHEN is_gmp = '是' THEN 1 ELSE 0 END AS is_gmp, zhuzlx AS effect, shangplx AS drug_category_id, kcsx AS storage_upper, kcxx AS storage_floor, yanghffcsh AS conserve_measure, CASE WHEN leibie = '中药饮片' THEN 0 ELSE 1 END AS is_drug_piece, CASE WHEN leibie = '医疗器械类' THEN 0 ELSE 1 END AS instrumnet_type_id, CASE WHEN leibie = '保健食品' THEN 0 ELSE 1 END AS is_health_food, CASE WHEN leibie = '食品' THEN 0 ELSE 1 END AS is_food, lastmodifytime AS create_time, GETDATE() AS update_time, 1 AS status FROM spkfk  {WHERE_LIST}  ORDER BY lastmodifytime, spbh;";
        if (MyStrUtil.isNotEmpty(addDate)) {
            sql = sql.replace("{WHERE_LIST}", " WHERE  lastmodifytime >= '" + addDate + "' ");
        } else {
            sql = sql.replace("{WHERE_LIST}", " ");
        }
        Connection connection = null;
        List<BjaProductEntity> products = new ArrayList<>();
        try {
            connection = SqlJDBCUtil.getConnection(B2BConstant.BJA.getCompanyCode());
            ResultSet resultSet = SqlJDBCUtil.execQuery(sql, connection);
            products = ChangeUtil.changeResultToList(resultSet, BjaProductEntity.class);
        } finally {
            SqlJDBCUtil.commit(connection);
        }
        if (products.isEmpty()) {
            return;
        }
        //2.插入到对应前缀的商品表
        bjaProductService.saveOrUpdateBatch(products);

    }

    @Override
    public void outOrderDataTransport() {
        if (!checkNeedThirdData()) {
            return;
        }
        //1配货单
        Date maxHeadQuarterAllotDate = bjaOrderOutService.selectMaxHeadQuarterAllotDate(1);
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxHeadQuarterAllotDate)) {
            addDate = MyDateUtil.formatDateTime(maxHeadQuarterAllotDate);
        } else {
//            throw new CustomerException("同步出库单时出库时间不能为空,否则查询全部数据!!!");
            addDate = DEFAULT_DOWNLOAD_ORDER_OUT_TIME;
        }
        //1.从ZY查询出库单
        String sql = B2BConstant.BJA_PULL_OUT_ORDER_SQL.getSql().replace("{WHERE_LIST}", " and convert(varchar(100), z.kaiprq + ' ' + z.ontime, 120) > '" + addDate + "' ");
        Connection connection = null;
        List<BjaOrderOutEntity> orderOuts = new ArrayList<>();
        try {
            connection = SqlJDBCUtil.getConnection(B2BConstant.BJA.getCompanyCode());
            ResultSet resultSet = SqlJDBCUtil.execQuery(sql, connection);
            orderOuts = ChangeUtil.changeResultToList(resultSet, BjaOrderOutEntity.class);
            //出库和退货不在同一张表,查询退货单
            List<BjaOrderOutEntity> backOrderOuts = this.selectBackOrders(connection);
            orderOuts.addAll(backOrderOuts);
        } finally {
            SqlJDBCUtil.commit(connection);
        }
        if (orderOuts.isEmpty()) {
            return;
        }
        //2.插入到对应前缀的出库表
        bjaOrderOutService.saveOrUpdateBatch(orderOuts);

    }

    /**
     * 查询退货单据
     *
     * @param connection
     */
    private List<BjaOrderOutEntity> selectBackOrders(Connection connection) {
        Date maxHeadQuarterAllotDate = bjaOrderOutService.selectMaxHeadQuarterAllotDate(2);
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxHeadQuarterAllotDate)) {
            addDate = MyDateUtil.formatDateTime(maxHeadQuarterAllotDate);
        } else {
            addDate = DEFAULT_DOWNLOAD_ORDER_OUT_TIME;
        }
        //1.从ZY查询退货单
        String sql = "SELECT ( SELECT hwbh FROM huoweizl WHERE hw = z.dchw ) AS store_code, z.kaiprq + ' ' + z.ontime AS head_quarter_allot_date, " +
                " rtrim(z.djbh) AS head_quarter_allot_no, rtrim(z.djbh) + '_' + CONVERT (VARCHAR(8), m.dj_sn) AS key_no, 2 AS out_type, s.spbh AS product_code, " +
                " s.spmch AS product_name, s.tongym AS generic_name, s.shpgg AS standard, m.dw AS unit, s.shpchd AS producing_area, s.shengccj AS produce_factory, s.pizhwh AS approval_no, s.jixing AS dosage_form, " +
                " 0 - m.shl AS purchase_amount, 0 - m.shl AS qualified_amount, m.dbdj AS purchase_price, 0 - m.dbje AS purchase_fee, m.pihao AS batch_no, " +
                " m.baozhiqi AS produce_date, m.sxrq AS expiry_date, z.username AS create_user_name, " +
                " z.kaiprq + ' ' + z.ontime AS create_time, GETDATE() AS update_time, m.yuany AS remark FROM dbhz z, dbmx m, spkfk s " +
                " WHERE m.djbh = z.djbh AND m.spid = s.spid AND z.dchw IN ( SELECT hw FROM huoweizl WHERE hwbh IN (SELECT duifbsh FROM lsdtxb) ) {WHERE_LIST} ORDER BY z.kaiprq;";
        sql = sql.replace("{WHERE_LIST}", " AND convert(varchar(100), z.kaiprq + ' ' + z.ontime, 120) > '" + addDate + "' ");
        ResultSet resultSet = SqlJDBCUtil.execQuery(sql, connection);
        return ChangeUtil.changeResultToList(resultSet, BjaOrderOutEntity.class);
    }

    @Override
    public void uploadMemberData() {
        if (!checkNeedThirdData()) {
            return;
        }
        Date maxModifyDate = bjaUploadMemberDataService.selectMaxModifyDate();
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxModifyDate)) {
            addDate = MyDateUtil.formatDateTime(maxModifyDate);
        } else {
            addDate = DEFAULT_DOWNLOAD_ORDER_OUT_TIME;
        }
        String storeIds = getStoreIds();
        String sql = B2BConstant.BJA_UPLOAD_MEMBER_DATA_SQL.getSql().replace("{STORE_IDS}", storeIds).replace("{WHERE_LIST}", " and info.modifyDate > '" + addDate + "' ");
        List<BjaUploadMemberDataEntity> memberDatas = getMemberData(sql);
        if (memberDatas.isEmpty()) {
            return;
        }
        bjaUploadMemberDataService.saveOrUpdateBatch(memberDatas);
    }

    //查询门店的id,用于会员中心查询会员信息
    private String getStoreIds() {
        String sql = "SELECT store_id FROM base_supplier WHERE data_download_code = '{DOWNLOAD_CODE}' ";
        sql = sql.replace("{DOWNLOAD_CODE}", B2BConstant.BJA.getCompanyCode());
        List<StoreModel> storeModels = this.getDataBySql(sql, StoreModel.class);
        if (storeModels.isEmpty()) {
            return " '' ";
        }
        Map<String, String> map = new ConcurrentHashMap<>();
        //去重
        StringBuilder sb = new StringBuilder();
        for (StoreModel storeModel : storeModels) {
            String storeId = storeModel.getStoreId();
            if (!map.containsKey(storeId)) {
                map.put(storeId, storeId);
                sb.append("'").append(storeId).append("'").append(",");
            }
        }
        return sb.deleteCharAt(sb.length() - 1).toString();
    }

    private List<BjaUploadMemberDataEntity> getMemberData(String sql) {
        Connection connection = null;
        try {
            connection = MysqlJDBCUtil.getConnection(B2BConstant.MEMBER.getCompanyCode());
            ResultSet resultSet = MysqlJDBCUtil.execQuery(sql, connection);
            return  ChangeUtil.changeResultToList(resultSet, BjaUploadMemberDataEntity.class);
        } finally {
            MysqlJDBCUtil.close(connection);
        }
    }

    @Override
    public void uploadSaleData() {
        if (!checkNeedThirdData()) {
            return;
        }
        Date maxCreateDate = bjaUploadSalesDataService.selectMaxCreateDate();
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxCreateDate)) {
            addDate = MyDateUtil.formatDateTime(maxCreateDate);
        } else {
            addDate = DEFAULT_DOWNLOAD_ORDER_OUT_TIME;
        }
        //B2BConstant.BJA_UPLOAD_SALE_DATA_SQL.getSql()
        String sql = "SELECT sod.saleOrderDetailId AS saleOrderId, so.storeId AS storeCode, saleOrderType, so.saleOrderNo, saleDate, memberNo, member AS 'memberName', productCode, productName, genericName, producingArea, standard, unit, approvalNumber, produceFactory, dosageForm, expiryDate, produceDate, batchNo, lotNo, sod.isRxDrug, sod.isEphedrine, isGMP, isSplitDrug, saleCount, costPrice, costAmount, retailPrice, retailAmount, memberPrice, memberAmount, discount, discountFee, sod.createDate, sod.modifyDate, operaterId, operater, sod.remark FROM saleOrder so, saleOrderDetail sod WHERE so.saleOrderNo = sod.saleOrderNo AND so.storeId IN (SELECT storeId FROM baseSupplier WHERE dataDownloadCode = 'BJA') {WHERE_LIST} ORDER BY so.storeId ;";
        sql = sql.replace("{WHERE_LIST}", " AND sod.create_time > '" + addDate + "' ");
        List<BjaUploadSalesDataEntity> saleDatas = getSaleData(sql);
        if (saleDatas.isEmpty()) {
            return;
        }
        bjaUploadSalesDataService.saveOrUpdateBatch(saleDatas);
    }

    private List<BjaUploadSalesDataEntity> getSaleData(String sql) {
        return getDataBySql(sql, BjaUploadSalesDataEntity.class);
    }

    @Override
    public void uploadStockData() {
        if (!checkNeedThirdData()) {
            return;
        }
        Date maxModifyDate = bjaUploadStockDataService.selectMaxModifyDate();
        String addDate = null;
        if (MyStrUtil.isNotEmpty(maxModifyDate)) {
            addDate = MyDateUtil.formatDateTime(maxModifyDate);;
        } else {
            addDate = DEFAULT_DOWNLOAD_ORDER_OUT_TIME;
        }
        String sql = B2BConstant.BJA_UPLOAD_STOCK_DATA_SQL.getSql().replace("{WHERE_LIST}", " AND update_time > '" + addDate + "' ");
        List<BjaUploadStockDataEntity> stockDatas = getStockData(sql);
        if (stockDatas.isEmpty()) {
            return;
        }
        bjaUploadStockDataService.saveOrUpdateBatch(stockDatas);
    }

    private List<BjaUploadStockDataEntity> getStockData(String sql) {
        return getDataBySql(sql, BjaUploadStockDataEntity.class);
    }

    private <T> List<T> getDataBySql(String sql, Class<T> clazz) {
        Connection connection = null;
        List<T> datas = new ArrayList<>();
        try {
            connection = MysqlJDBCUtil.getConnection(B2BConstant.YAODIAN.getCompanyCode());
            ResultSet resultSet = MysqlJDBCUtil.execQuery(sql, connection);
            List<T> prefixList = ChangeUtil.changeResultToList(resultSet, clazz);
            datas.addAll(prefixList);
        } finally {
            MysqlJDBCUtil.close(connection);
        }
        return datas;
    }

    @Override
    public boolean checkNeedThirdData() {
        return ChangeUtil.checkNeedThridDataService(B2BConstant.BJA.getCompanyCode());
    }

    @Override
    public void convertInspectionOrderData() {
        if (!checkNeedThirdData()) {
            return;
        }
        //1.查询未转换的单据单号和总金额,根据单号查询主单和明细
        List<BjaInspectionOrderEntity> outOrders = bjaInspectionOrderService.listInspectionOrders(B2BConstant.MAX_CONVERT_OUT_ORDER_LIMIT_SIZE.getNumber());
        //根据单号,查询该单号是否存在,存在则忽略,不存在,查询明细
        List<BjaInspectionOrderDetailEntity> orderDetails = new ArrayList<>();
        Iterator<BjaInspectionOrderEntity> iterator = outOrders.iterator();
        while (iterator.hasNext()) {
            BjaInspectionOrderEntity order = iterator.next();
            boolean hasOrder = bjaInspectionOrderService.findHasOrderByHeadNo(order.getHeadQuarterAllotNo());
            if (hasOrder) {
                iterator.remove();
            } else {
                //查询明细,添加明细到待插入列表.
                List<BjaInspectionOrderDetailEntity> listDetails = bjaInspectionOrderDetailService.listOutOrderDetails(order.getHeadQuarterAllotNo());
                orderDetails.addAll(listDetails);
            }
        }
        if (outOrders.isEmpty()) {
            return;
        }
        //2.插入主单到xxOutOrder,明细到xxOutOrderDetail //3.更新已插入的单号的状态
        //插入主表和明细.
        bjaInspectionOrderService.insertByListAndUpdateState(outOrders, orderDetails);
    }

    @Override
    public void storeDataToAdminTransport() {
        // do nothing
    }

}
